package com.bosch.in.rtp1.ept.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.bosch.in.rtp1.ept.constant.CommonConstants;
import com.bosch.in.rtp1.ept.constant.QueryKeyWord;
import com.bosch.in.rtp1.ept.constant.ReleasedHoldColumnName;
import com.bosch.in.rtp1.ept.dao.ReleasedHoldDao;
import com.bosch.in.rtp1.ept.model.dto.ReleasedHold;
import com.bosch.in.rtp1.ept.model.technical.Condition4ReleasedHold;
import com.bosch.in.rtp1.ept.util.SQLStatementLoader;

/**
 * Concrete JDBC implementation of FinishRecipeDao. Provide actions for searching on Finish Recipes table.
 * @author LIV1HC
 *
 */
public class ReleasedHoldJDBCDao extends GenericDao implements ReleasedHoldDao{

	private static final Logger LOGGER = Logger.getLogger(ReleasedHoldJDBCDao.class);
	
	/**
	 * Constructor to inject connection
	 * @param con	DB connection
	 */
	
	public ReleasedHoldJDBCDao(Connection connection) {
		super(connection);
	}

	@Override
	public List<ReleasedHold> getReleasedHolds(Condition4ReleasedHold searchCriteria)
			throws SQLException {
		LOGGER.debug("Begin getReleasedHolds");
		String queryString = searchCriteria.getSqlQuery();
		ResultSet resultSet = executeQuery(queryString, searchCriteria.getParameterValues().toArray());
		
		List<ReleasedHold> releasedHolds = new ArrayList<ReleasedHold>();
		
		DecimalFormat df = new DecimalFormat(CommonConstants.NUMBER_FORMAT);
		
		while (resultSet.next()) {
			ReleasedHold releasedHold = new ReleasedHold();
			releasedHold.setLotId(resultSet.getString(ReleasedHoldColumnName.LOT_ID_DB));
			float holdDuration = Float.valueOf(df.format(resultSet.getFloat(ReleasedHoldColumnName.HELD_DURATION_DB)));
			releasedHold.setHeldDuration(holdDuration);
			releasedHold.setWaferNumber(resultSet.getInt(ReleasedHoldColumnName.TRACKIN_MAIN_QTY_DB));
			releasedHold.setPartId(resultSet.getString(ReleasedHoldColumnName.PART_ID_DB));
			releasedHold.setLotType(resultSet.getString(ReleasedHoldColumnName.LOT_TYPE_DB));
			releasedHold.setCustomerName(resultSet.getString(ReleasedHoldColumnName.CUSTOMER_NAME_DB));
			releasedHold.setMaxPriority(resultSet.getInt(ReleasedHoldColumnName.PRIORITY_DB));
			releasedHold.setProdArea(resultSet.getString(ReleasedHoldColumnName.PRO_AREA_DB));
			releasedHold.setLocation(resultSet.getString(ReleasedHoldColumnName.LOCATION_DB));
			releasedHold.setStage(resultSet.getString(ReleasedHoldColumnName.STAGE_DB));
			releasedHold.setRecipeId(resultSet.getString(ReleasedHoldColumnName.RECIPE_ID_DB));
			releasedHold.setRecipeTitle(resultSet.getString(ReleasedHoldColumnName.RECIPE_TITLE_DB));
			releasedHold.setHaltStart(resultSet.getTimestamp(ReleasedHoldColumnName.HALT_START_DB));
			releasedHold.setHaltEnd(resultSet.getTimestamp(ReleasedHoldColumnName.HALT_END_DB));
			releasedHold.setHoldCode(resultSet.getString(ReleasedHoldColumnName.HOLD_CODE_DB));
			releasedHold.setHoldComment(resultSet.getString(ReleasedHoldColumnName.HOLD_COMMENT_DB));
			releasedHold.setReleasedComment(resultSet.getString(ReleasedHoldColumnName.RELEASED_COMMENT_DB));
			releasedHold.setHoldCodeReclassified(resultSet.getString(ReleasedHoldColumnName.HOLD_CODE_RECLASSIFIED_DB).trim());
			releasedHold.setHoldCommentReclassified(resultSet.getString(ReleasedHoldColumnName.HOLD_COMMENT_RECLASSIFIED_DB).trim());
			
			releasedHolds.add(releasedHold);
		}
		// close statement, result set
		resultSet.close();
		close();
		LOGGER.debug("End getReleasedHolds");
		return releasedHolds;
	}

	@Override
	public boolean updateReleasedHolds(List<ReleasedHold> releaseHolds)
			throws SQLException {
		LOGGER.debug("Begin updateReleasedHolds");
		try {
			if (releaseHolds.size() == 0) {
				// do nothing
				return true;
			}
			// convert from string to date
		    SimpleDateFormat sdf = new SimpleDateFormat(CommonConstants.FULL_DATE_FORMAT);
		    
			String updateStm = SQLStatementLoader.getReleasedHoldUpdate();
			
			// set parameter values for updated frozen columns
			Object[][] params = new Object[releaseHolds.size()][];
			for (int i = 0; i < releaseHolds.size(); i++) {
				params[i] = new Object[7];
				// trick for Oracle, distinguish empty with NULL by replacing empty by BLANK
				if (releaseHolds.get(i).getHoldCodeReclassified().isEmpty()) {
					releaseHolds.get(i).setHoldCodeReclassified(QueryKeyWord.BLANK);
				}
				params[i][0] = releaseHolds.get(i).getHoldCodeReclassified();
				
				// trick for Oracle, distinguish empty with NULL by replacing empty by BLANK
				if (releaseHolds.get(i).getHoldCommentReclassified().isEmpty()) {
					releaseHolds.get(i).setHoldCommentReclassified(QueryKeyWord.BLANK);
				}
				params[i][1] = releaseHolds.get(i).getHoldCommentReclassified();
				params[i][2] = releaseHolds.get(i).getLotId();
				params[i][3] = releaseHolds.get(i).getRecipeId();
				params[i][4] = releaseHolds.get(i).getStage();
				params[i][5] = sdf.format(releaseHolds.get(i).getHaltStart()).toUpperCase();
				params[i][6] = sdf.format(releaseHolds.get(i).getHaltEnd()).toUpperCase();
				
			}
			
			return executeBatchUpdate(updateStm, params);
		} finally {
			LOGGER.debug("End updateReleasedHolds");
		}
	}
}
